package com.agu.Excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.Set;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.agu.Excel.ExcelReader.ExcelType;

public class AddressReader {

	public AddressReader() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * 创建工作簿对象
	 * 
	 * @param filePath
	 * @return
	 * @throws IOException
	 * @date 2013-5-11
	 */
	public static Workbook createWb(InputStream inputStream, ExcelType type) {
		try {
			if (type == null || inputStream == null) {
				return null;
			} else if (type.equals(ExcelType.XLS)) {
				return new HSSFWorkbook(inputStream);
			} else if (type.equals(ExcelType.XLSX)) {
				return new XSSFWorkbook(inputStream);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Workbook createWb(String fileName) {
		String s = fileName.toLowerCase();

		try {
			if (s.endsWith("xls")) {
				return new HSSFWorkbook(new FileInputStream(fileName));
			} else if (s.endsWith("xlsx")) {
				return new XSSFWorkbook(new FileInputStream(fileName));
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Sheet getSheet(Workbook wb, String sheetName) {
		return wb.getSheet(sheetName);
	}

	public static Sheet getSheet(Workbook wb, int index) {
		return wb.getSheetAt(index);
	}

	private static String getStringCellValue(Cell cell) {// 获取单元格数据内容为字符串类型的数据
		if (cell == null) {
			return "";
		}
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				double d = cell.getNumericCellValue();
				Date date = HSSFDateUtil.getJavaDate(d);
				SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				strCell = sFormat.format(date);
				break;
			}
			strCell = String.valueOf((long) cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		return strCell.trim();
	}
	
	public static Set readerMobils(String file) {
		
		Workbook wb = createWb(file);
		Sheet sheet = ExcelReader.getSheet(wb, 0);

		int rowNum = sheet.getLastRowNum(); // 得到总行数
		Row row = sheet.getRow(0);// 得到标题的内容对象。
		// int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。
		
		String reg = "^[1][0-9]{10}$";
		Pattern p = Pattern.compile(reg);
		
		Set mobiles = new LinkedHashSet();

		for (int i = 1; i <= rowNum; i++) { // 正文内容应该从第二行开始,第一行为表头的标题
			row = sheet.getRow(i);
			if (row != null) {
				int j = 0;
				String mobile = getStringCellValue(row.getCell(j)).trim();
				
				if (Pattern.matches(reg, mobile.trim())) {
					mobiles.add(mobile.trim());
				}
				
				System.out.println(mobile);
			}
		}
		
		return mobiles;

	}

	public static void main1(String[] args) {
		String file = "/Users/agu/Documents/c.xlsx";
		Workbook wb = createWb(file);
		Sheet sheet = ExcelReader.getSheet(wb, 0);

		int rowNum = sheet.getLastRowNum(); // 得到总行数
		Row row = sheet.getRow(0);// 得到标题的内容对象。
		// int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。
		
		String reg = "^[1][0-9]{10}$";
		Pattern p = Pattern.compile(reg);
		
		Set mobiles = new LinkedHashSet();

		for (int i = 1; i <= rowNum; i++) { // 正文内容应该从第二行开始,第一行为表头的标题
			row = sheet.getRow(i);
			if (row != null) {
				int j = 0;
				String mobile = getStringCellValue(row.getCell(j)).trim();
				
				if (Pattern.matches(reg, mobile.trim())) {
					mobiles.add(mobile.trim());
				}
				
				System.out.println(mobile);
			}
		}

	}
	
	public static void main(String[] args) {
		String file = "/Users/agu/Documents/c.xlsx";
		
		Set mobiles = AddressReader.readerMobils(file);
		
	}

}
